package Schema;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
/*
* The following imports are commented away since the e-mail functionality is currently disabled
*/
//import org.apache.commons.mail.DefaultAuthenticator;
//import org.apache.commons.mail.Email;
//import org.apache.commons.mail.EmailException;
//import org.apache.commons.mail.SimpleEmail;

/**
 *
 * @author Lars Malmqvist
 */

/*
* This bean will be used by visaSchema.xhtml
*/
@ManagedBean(name="schema")
@RequestScoped
public class Schema {

    private static Calendar cal = Calendar.getInstance();
    private ArrayList<String> employees;
    private ArrayList<String> messages;
    private Connection connection;
    private String url;
    private String username;
    private String password;

    /**
     * Creates a new instance of Schema
     * @throws java.sql.SQLException
     */
    
    public Schema() throws SQLException { 
        messages = new ArrayList<>();
        employees = new ArrayList<>();
        
        /*
        * Set up connection to the mySQL-database
        */
        url = "jdbc:mysql://pellos.no-ip.org:3306/antons_skafferi";
        username = "anders";
        password = "anton";
        connection = DriverManager.getConnection(url, username, password);
    }
    
    /*
    * Method to set the week.
    * Calls getWeek to get the week currently used in this class.
    * Takes one parameter, which is weekUpdater from visaSchema.xhtml, and updates the calendar.
    */
    public void setWeek(int weekUpdater) {    
        cal.set(Calendar.WEEK_OF_YEAR, getWeek());
        cal.add(Calendar.WEEK_OF_YEAR, weekUpdater);
    }
    
    /*
    * Method for returning the week that is currently set in the class.
    */
    public int getWeek() {
        return cal.get(Calendar.WEEK_OF_YEAR);
    }
    
    /*
    * Sets and returns the current date that is to be used in the class.
    * Takes one parameter, which is dayOfWeek, from visaSchema.xhtml.
    * This integer is numbers from 0 to 6 representing the day of week (0=Monday, 1=Tuesday, etc...)
    */
    public String currentDate(int dayOfWeek) {
        Calendar calendar = Calendar.getInstance(); //create new calendar (TODO: Check why a new calendar is needed)
        calendar.set(Calendar.WEEK_OF_YEAR, getWeek()); //set the week currently displayed
        calendar.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);    //set it to monday
        calendar.add(Calendar.DAY_OF_WEEK, dayOfWeek);  //and then add the integer which tells us the day we're currently on
        
        /*
        * To search in the database using the date, it has to be set up in a specific way
        * Therefor we create a new date variable and format it accordingly. 
        */
        Date now = calendar.getTime();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String date = sdf.format(now);
        return date;      
    }
    
    /*
    * Method for storing the current date from the calendar in a static variable in the class "Datum"
    * Also stores the typeOfPass (lunch/middag).
    * Takes two parameters from visaSchema.xhtml.
    */
    public void setDatumInClassDatum(int dayOfWeek, String typeOfPass){
        String tempDate = currentDate(dayOfWeek);  //calls currentDate to get the date we're on
        Datum.setDate(tempDate);
        Datum.setType(typeOfPass);
    }
    
    /*
    * This is a simply little method that returns the background-color to be used by for the shifts.
    * Takes three parameters from visaSchema.xhtml: dayOfWeek (0-6), typeOfPass (lunch/middag) and thisUser (request.remoteUser).
    */
    public String color(int dayOfWeek, String typeOfPass, String thisUser) throws SQLException {
        /*
        * This method calls the method getStatus which makes the database question.
        * The value retrieved is then passed on to visaSchema.xhtml.
        * TODO: Combine color and getStatus into one method.
        */
        String color = getStatus(dayOfWeek, typeOfPass, thisUser);
        if (color.equals("green")) {
            return "green";
        }
        else if (color.equals("orange")) {
            return "orange";
        }
        else if (color.equals("blue")) {
            return "blue";
        }
        else {
            return "white";
        }   
    }
    
    /*
    * This method is called by the method color.
    * It ask a question to the database if the logged in user is on the specific shift.
    * If so, it returns the color from the column status.
    */
    public String getStatus(int dayOfWeek, String typeOfPass, String thisUser) throws SQLException {
        String date = currentDate(dayOfWeek);  //Get the date we're currently on
        String color = null;    //A variable for storing the color
        
        /*
        * SQL-question to the db. Uses the variables from the method color.
        */
        PreparedStatement getStatus = connection.prepareStatement("SELECT * FROM Shifts s, EmployeeShifts e WHERE s.shift_time='" + typeOfPass + "' AND e.username='" + thisUser + "' AND s.shift_date='" + date + "' AND s.shift_ID = e.shift_ID");
        ResultSet result = getStatus.executeQuery();    //Stores the result in a variable
        
        /*
        * Checks if there is a result, otherwise return "white" (meaning, the user isn't on the shift at all.
        * There can only be one value in result, since an employee can only work once on a shift.
        */
        if (result.next()) {
            color = result.getString("status"); //get value from the status column.
            return color;
        }
        else {
            return "white";
        }
    }
    
    /*
    * This method is used when a user has sent a question to another employee on trading shifts.
    * Four parameters from visaSchema.xhtml: date (which comes from the static variable in the class Datum, thisUser (request.remoteUser),
    * reciever (the value on the button clicked), and typeOfPass (also from the class Datum).
    * E-mail is currently disabled from this method.
    */
    public void sendMailAndUpdateDB(String date, String thisUser, String reciever, String typeOfPass) throws /*EmailException,*/ SQLException{
        //String epostAdress = reciever;
        //epostAdress += "@antons.se";
        //Email email = new SimpleEmail();
        //email.setHostName("smtp.gmail.com");
        //email.setSmtpPort(587);
        //email.setAuthenticator(new DefaultAuthenticator("antons.passbyten", "stege123"));
        //email.setStartTLSEnabled(true);
        //email.setFrom("antons.passbyten@gmail.com");
        //email.setSubject("" + thisUser + " vill byta pass med dig");
        //email.setMsg("" + thisUser + "vill byta plats med dig den " + date + ".");
        //email.addTo(epostAdress);
        //email.send();
        
        /*
        * The SQL-questions for updating the database.
        */
        PreparedStatement createMessage = connection.prepareStatement("INSERT INTO messages (sender, reciever, date) VALUES ('" + thisUser + "', '" + reciever + "', '" + date + "')");
        createMessage.execute();    //This one creates a new row in the table "messages"
        PreparedStatement setSenderStatus = connection.prepareStatement("UPDATE Shifts s, EmployeeShifts e SET s.status='orange' WHERE s.shift_ID = e.shift_ID AND e.username='" + thisUser + "' AND shift_date='" + date + "' AND shift_time='" + typeOfPass + "'");
        setSenderStatus.execute();  //This one changes the color of the pass to orange for the user. 
        PreparedStatement setRecieverStatusShifts = connection.prepareStatement("INSERT INTO Shifts (shift_date, shift_time, status) VALUES ('" + date + "', '" + typeOfPass + "', 'blue')");
        setRecieverStatusShifts.execute();    //This one creates a new, blue, shift in the table Shifts ...
        
        /*
        * ... but we also need to map that shift to the other employee (reciever) ...
        */
        PreparedStatement findID = connection.prepareStatement("SELECT shift_ID FROM Shifts WHERE shift_date='" + date + "' AND shift_time='" + typeOfPass + "' AND status='blue'");
        ResultSet result = findID.executeQuery();   // ... for that, we need the ID from the shift that was created above.
        String foundID = null;  //create a variable for storing the ID
        while (result.next()) { //get the ID (should only be one value in result).
            foundID = result.getString("shift_ID");
        }
        PreparedStatement setRecieverStatusES = connection.prepareStatement("INSERT INTO EmployeeShifts VALUES ('"+ reciever + "', '" + foundID + "')");
        setRecieverStatusES.execute();  //Now we can map the shift to an employee in the table EmployeeShifts.
    }
    
    /*
    * This method is called one a user has accepted the new shift.
    * Three parameters from visaSchema.xhtml: date (comes from the static variable in the class Datum), thisUser (request.remoteUser),
    * typeOfPass (also from the class Datum).
    */
    public void acceptChangeShift(String date, String thisUser, String typeOfPass) throws SQLException {
        /*
        * The SQL-questions for the database.
        */
        PreparedStatement changeRecieverStatus = connection.prepareStatement("UPDATE Shifts s, EmployeeShifts e SET s.status='green' WHERE s.shift_ID = e.shift_ID AND e.username='" + thisUser + "' AND shift_date='" + date + "' AND shift_time='" + typeOfPass + "'");
        changeRecieverStatus.execute(); //This one sets the color of the shift to green for the user.
        
        /*
        * Then we need to delete the shift from the other employee who sent the question.
        */
        PreparedStatement findSender = connection.prepareStatement("SELECT sender FROM messages WHERE reciever='" + thisUser + "' AND date='" + date + "'");
        ResultSet result = findSender.executeQuery();   //This one is used to find out which employee sent the question
        String sender = null;
        while (result.next()) { //we find the name in the result set (should only be on name there)
            sender = result.getString("sender");    
        }
        
        /*
        * Now we use the this name, status, date and type of pass, 
        * to find the ID of the pass so it can be deleted from both tables.
        */
        PreparedStatement findID = connection.prepareStatement("SELECT s.shift_ID FROM Shifts s, EmployeeShifts e WHERE s.shift_date='" + date + "' AND s.shift_time='" + typeOfPass + "' AND s.status='orange' AND e.username='" + sender + "'");
        ResultSet resultID = findID.executeQuery(); //This one finds the ID of the shift.
        String foundID = null;
        while (resultID.next()) {   //We get the ID from the result set (should only be one in there).
            foundID = resultID.getString("shift_ID");
        }
        PreparedStatement changeSenderStatus2 = connection.prepareStatement("DELETE FROM EmployeeShifts WHERE shift_ID='" + foundID + "'");
        changeSenderStatus2.execute();  //This one deletes the row from the EmployeeShifts table.
        PreparedStatement changeSenderStatus = connection.prepareStatement("DELETE FROM Shifts WHERE shift_ID='" + foundID + "'");
        changeSenderStatus.execute();   //This one deletes the row from the Shifts table.
        PreparedStatement deleteFromMessages = connection.prepareStatement("DELETE FROM messages WHERE reciever='" + thisUser + "' AND date='" + date + "'");
        deleteFromMessages.execute();   //This one deletes the row from the messages table.
    }
    
    /*
    * This method is called one a user has declined the new shift.
    * Three parameters from visaSchema.xhtml: date (comes from the static variable in the class Datum), thisUser (request.remoteUser),
    * typeOfPass (also from the class Datum).    
    */
    public void declineChangeShift(String date, String thisUser, String typeOfPass) throws SQLException {
        /*
        * The SQL-questions for the database.
        */
        PreparedStatement findSender = connection.prepareStatement("SELECT sender FROM messages WHERE reciever='" + thisUser + "' AND date='" + date + "'");
        ResultSet result = findSender.executeQuery();   //This one is used to find out which employee sent the question
        String sender = null;
        while (result.next()) { //we find the name in the result set (should only be on name there)
            sender = result.getString("sender");    
        }
        PreparedStatement findID = connection.prepareStatement("SELECT s.shift_ID FROM Shifts s, EmployeeShifts e WHERE s.shift_date='" + date + "' AND s.shift_time='" + typeOfPass + "' AND s.status='blue' AND e.username='" + sender + "'");
        ResultSet resultID = findID.executeQuery(); //This one is used to find out the ID of the shift.        
        String foundID = null;
        while (resultID.next()) { //we traverse through the result set which should only have one value.
            foundID = resultID.getString("shift_ID");
        }
        PreparedStatement changeRecieverStatusES = connection.prepareStatement("DELETE FROM EmployeeShifts WHERE shift_ID='" + foundID + "'");
        changeRecieverStatusES.execute();   //This one deletes the row from the EmployeeShifts table.
        PreparedStatement changeRecieverStatusShifts = connection.prepareStatement("DELETE FROM Shifts WHERE shift_ID='" + foundID + "'");
        changeRecieverStatusShifts.execute();    //This one deletes the row from the Shifts table.
        PreparedStatement changeSenderStatus = connection.prepareStatement("UPDATE Shifts s, EmployeeShifts e SET s.status='green' WHERE s.shift_ID = e.shift_ID AND e.username='" + sender + "' AND shift_date='" + date + "' AND shift_time='" + typeOfPass + "'");
        changeSenderStatus.execute();   //This one changes the color back to green for the other employee (sender).
        PreparedStatement deleteFromMessages = connection.prepareStatement("DELETE FROM messages WHERE reciever='" + thisUser + "' AND date='" + date + "'");
        deleteFromMessages.execute();   //This one deletes the row from the messages table.
    }
    
    /*
    * This method is used to map all the names of the employees to the buttons on visaSchema.xhtml.
    * We need one parameter, thisUser, so employees can't send trade requests to themselves.
    */
    public ArrayList<String> displayEmployees(String thisUser) throws SQLException{
        /*
        * SQL-question to the database: Get everything from the table Employees.
        */
        PreparedStatement getEmployees = connection.prepareStatement("SELECT * FROM Employees");
        ResultSet result = getEmployees.executeQuery();
        
        /*
        * Traverse through the result set and store the value from the column emplyee_name in a variable.
        * And then add this variable to the ArrayList,
        * unless the value is null or already in the list.
        */
        String employee = null;
        while (result.next()){
            employee = result.getString("emplyee_name");
            if (!(employees.contains(employee)) && (employee == null ? thisUser != null : !employee.equals(thisUser))) {
                employees.add(employee);
            }
        }
        return employees; 
    }
    
    /*
    * This method is used to display all the ongoing shift trades with the employees.
    * Returns the ArrayList messages.
    */
    public ArrayList<String> displayMessages() throws SQLException {
        /*
        * SQL-question to the database: Get everything from the table messages.
        */
        PreparedStatement getMessagesTable = connection.prepareStatement("SELECT * FROM messages");
        ResultSet results = getMessagesTable.executeQuery();
        
        /*
        * Variables for storing data from the columns
        */
        String sender = null;
        String reciever = null;
        String date = null;
        String message = null;
        
        /*
        * Traverse through the result set and store the column values in the variables
        * Then combine those into a new String called message.
        * Then add that String to the ArrayList.
        */
        while (results.next()){
            sender = results.getString("sender");
            reciever = results.getString("reciever");
            date = results.getString("date");
            message = sender + " vill byta pass med " + reciever + " den " + date;
            if (!(messages.contains(message))) {
                messages.add(message);
            }
        }
        return messages; 
    }
    
}   //end of class.
